﻿using Lamex.Base;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace Lamex.mSA.Common
{
    public class CRUDHelper
    {
        /// <summary>
        /// 批量添加数据
        /// </summary>
        /// <param name="dt">添加到数据库的DataTable</param>
        /// <param name="TableName">数据库表名</param>
        /// <returns></returns>
        public static bool Insert_Batch(DataTable dt, string TableName)
        {
            if (BaseBuildSql.ExcuteInsert(dt, TableName))
                return true;
            else
                return false;
        }
        //public static bool ExcuteInsert(DataTable dt, string TableName)
        //{

        //    try
        //    {
        //        string sConn = SqlConfiguration.CurrentConnectionString;
        //        SqlConnection connection = new SqlConnection(sConn);
        //        connection.Open();
        //        SqlBulkCopy sqlbulkcopy = new SqlBulkCopy(connection);
        //        //sqlbulkcopy.BulkCopyTimeout = 100;  //超时之前操作完成所允许的秒数
        //        //sqlbulkcopy.BatchSize = dt.Rows.Count;  //每一批次中的行数
        //        sqlbulkcopy.DestinationTableName = TableName;  //服务器上目标表的名称
        //        for (int i = 0; i < dt.Columns.Count; i++)
        //        {
        //            // sqlbulkcopy.ColumnMappings.Add(i, i);  //映射定义数据源中的列和目标表中的列之间的关系
        //            sqlbulkcopy.ColumnMappings.Add(dt.Columns[i].ColumnName, dt.Columns[i].ColumnName);
        //        }
        //        sqlbulkcopy.WriteToServer(dt);  // 将DataTable数据上传到数据表中
        //        connection.Close();
        //        return true;
        //    }
        //    catch(Exception ex)
        //    {
        //        return false;
        //    }

        //}

        public static void Update_Table(DataTable dt)
        {
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                string update_sql = @"  UPDATE [LamexMESManager].[dbo].[SA_CBM_WT_From_SAP] SET DISABLE_FLAG=1,UPDATE_TIME=GETDATE(),UPDATE_USER='" + Lamex.Common.Loginer.CurrentUser.AccountName + @"'
   WHERE MATERIAL_NO='" + dt.Rows[i]["MATERIAL_NO"].ToString() + "' AND MATERIAL_VOLUME=" + dt.Rows[i]["MATERIAL_VOLUME"].ToString() + "" +
  " AND VOLUME_UNIT='" + dt.Rows[i]["VOLUME_UNIT"].ToString() + "' AND MATERIAL_WEIGHT=" + dt.Rows[i]["MATERIAL_WEIGHT"].ToString() + "" +
  " AND WEIGHT_UNIT='" + dt.Rows[i]["WEIGHT_UNIT"].ToString() + "'";
                Lamex.Base.BaseBuildSql.ExecuteNonQuery(update_sql);
            }

        }
        public static void Insert_SA_CBM_WT_ErrorLog(string strTRANSPORT_NO, string strMO_NO, string strMATERIAL_NO, string strMATERIAL_DESC,
            string strPART_NO, string strPART_DESC, string strLENGHT, string strWIDTH, string strHEIGHT, string strERROR_DESC)
        {
            string insert_sql = @" INSERT INTO [LamexMESManager].[dbo].[SA_CBM_WT_ErrorLog](
       [TRANSPORT_NO]
      ,[MO_NO]  
      ,[MATERIAL_NO]
      ,[MATERIAL_DESC]
	  ,[PART_NO]
      ,[PART_DESC]
      ,[LENGTH]
      ,[WIDTH]
      ,[HEIGHT]
      ,[ERROR_DESC]
      ,[UPDATE_TIME]
      ,[UPDATE_USER]
  )VALUES(
  '" + strTRANSPORT_NO + "','" + strMO_NO + "','" + strMATERIAL_NO + "','" + strMATERIAL_DESC.Replace("'","") + "'" +
  ",'" + strPART_NO + "','" + strPART_DESC.Replace("'","") + "','" + strLENGHT + "','" + strWIDTH + "','" + strHEIGHT + "'" +
  ",'" + strERROR_DESC + "',GETDATE(),'" + Lamex.Common.Loginer.CurrentUser.AccountName + "')";
            Lamex.Base.BaseBuildSql.ExecuteNonQuery(insert_sql);
        }

        public static void Delete_Table(DataTable dt)
        {
            List<string> TransportList = new List<string>();
            List<string> OrderList = new List<string>();
            List<string> LineNoList = new List<string>();
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                if (!string.IsNullOrEmpty(dt.Rows[i]["TRANSPORT_NO"].ToString().Trim()))
                    TransportList.Add(dt.Rows[i]["TRANSPORT_NO"].ToString().Trim());
                if (!string.IsNullOrEmpty(dt.Rows[i]["ORDER_NO"].ToString().Trim()))
                    OrderList.Add(dt.Rows[i]["ORDER_NO"].ToString().Trim());
                if (!string.IsNullOrEmpty(dt.Rows[i]["LINE_NO"].ToString().Trim()))
                    LineNoList.Add(dt.Rows[i]["LINE_NO"].ToString().Trim());
            }
            StringBuilder stringBuilder1 = new StringBuilder();
            stringBuilder1.AppendFormat(@" AND TRANSPORT_NO  IN ('{0}')", string.Join("','", TransportList.ToArray()));
            StringBuilder stringBuilder2 = new StringBuilder();
            stringBuilder2.AppendFormat(@" AND ORDER_NO  IN ('{0}')", string.Join("','", OrderList.ToArray()));
            StringBuilder stringBuilder3 = new StringBuilder();
            stringBuilder3.AppendFormat(@" AND LINE_NO  IN ('{0}')", string.Join("','", LineNoList.ToArray()));
            string sql = @"SELECT [TRANSPORT_NO] ,[ORDER_NO],[LINE_NO]
            FROM [LamexMESManager].[dbo].[SA_CBM_WT_Calculated] WHERE 1=1  " + stringBuilder1.ToString() + " " + stringBuilder2.ToString() + " " + stringBuilder3.ToString() + "  ";
           DataTable dataTable = Lamex.Base.BaseBuildSql.GetDataTableBySql(sql);
            if(dataTable.Rows.Count>0)
            {
                string delete_sql= @"delete from [LamexMESManager].[dbo].[SA_CBM_WT_Calculated] where 1=1 " + stringBuilder1.ToString() + " " + stringBuilder2.ToString() + " " + stringBuilder3.ToString() + " ";
                Lamex.Base.BaseBuildSql.ExecuteNonQuery(delete_sql);
            }
        }

        //查询Excel导出格式
        public static DataTable GetExportFormat(string listType, string filter)
        {
            string sql = @"SELECT a.TRANSPORT_NO as '出货通知单' 
,a.CUSTOMER_NAME as '客户名称'
,a.ORDER_NO as '销售订单' 
,a.LINE_NO as '项目'
,a.UP_LINE as '上层项目'
,a.MATERIAL_NO as 'SAP#' 
,a.MODEL_NO as '型号'
,a.MATERIAL_DESC as '产品描述'
,a.MRP_CONTROLLER as 'MRP'
,a.SL 
,a.SHIPPING_QTY as '出货数量'
,b.SHIPPING_VOLUME as '体积'
,b.SHIPPING_WEIGHT as '重量'
,'' as '车型'
,'' as '车量（辆数）'
,a.STOCK_QTY as '库存数量'
,'' as '送货次序'
,a.LOADING_DATE as '装车日'
,a.PRODUCT_LINE as '分厂'
            FROM
            (SELECT *
            FROM Transfer_SA_DN_List WHERE 1=1 AND"
             + " TRANSPORT_NO LIKE '" + listType + "%' " + filter + @") AS a
            LEFT JOIN SA_CBM_WT_Calculated AS b
            ON a.TRANSPORT_NO=b.TRANSPORT_NO AND a.ORDER_NO=b.ORDER_NO AND a.LINE_NO=b.LINE_NO
			order by a.TRANSPORT_NO,a.ORDER_NO,a.LINE_NO";
           DataTable table = BaseBuildSql.GetDataTableBySql(sql);
            return table;

        }
        //查询单位转换表
        public static DataTable Get_Unit_Convert()
        {
            string sql = @"SELECT [Main_Unit]
      ,[Convert_Unit]
      ,[Unit_DESC]
      ,[Convert_Value]
  FROM [LamexMESManager].[dbo].[Transfer_Unit_Convert]";
            DataTable table = BaseBuildSql.GetDataTableBySql(sql);
            return table;
        }
      
    }
}
